USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdAddress')
	BEGIN
		DROP  Procedure  dbo.InsUpdAddress
	END
GO

CREATE Procedure [dbo].[InsUpdAddress] (
	@ADDRESSGUID			VARCHAR(100)
,	@PARTNERGUID			VARCHAR(100)
,	@ADDRESSNO				VARCHAR(10)	 = ''
,	@STREET1				VARCHAR(100) = ''
,	@STREET2				VARCHAR(100) = ''
,	@CITY					VARCHAR(100) = ''
,	@STATE					VARCHAR(100) = ''
,	@ZIP					VARCHAR(100) = ''
,	@USERID					VARCHAR(10)	 = ''
-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
,	@ADDRESSTYPE			VARCHAR(10) = '2'
)
AS
BEGIN
	
	IF EXISTS(SELECT 1 FROM TR_ADDRESS WHERE CAST(AddressGUID AS VARCHAR(100)) = @ADDRESSGUID)
	BEGIN		-- UPDATE
		UPDATE TR_ADDRESS
		SET Street1		= @STREET1
		,	Street2		= @STREET2
		,	City		= @CITY
		,	[State]		= NULLIF(@STATE,'')
		,	Zip			= @ZIP
		,	ChangedBy	= @USERID
		,	ChangeDate	= GETDATE()
		WHERE
			AddressNo	= @ADDRESSNO
		AND	AddressType	= @ADDRESSTYPE
		AND	CAST(AddressGuid AS VARCHAR(100)) = @ADDRESSGUID
		AND	PartnerGuid	= @PARTNERGUID
	END
	ELSE
	BEGIN		-- INSERT 
		DECLARE @newAddressNo		INT
	
		SELECT @newAddressNo	= ISNULL(MAX(AddressNo), 0) +1 FROM TR_ADDRESS

		INSERT INTO TR_ADDRESS (AddressNo, AddressType, Street1, Street2, City, [State], Zip,
								CreatedBy,CreateDate,ChangedBy,ChangeDate,AddressGuid,PartnerGuid)
		VALUES(
			@newAddressNo		--AddressNo
		,	@ADDRESSTYPE		--AddressType
		,	@STREET1			--Street1
		,	@STREET2			--Street2
		,	@CITY				--City
		,	NULLIF(@STATE,'')	--State
		,	@ZIP				--Zip
		,	@USERID				--CreatedBy
		,	GETDATE()			--CreateDate
		,	@USERID				--ChangedBy
		,	GETDATE()			--ChangeDate
		,	CAST(@ADDRESSGUID AS UNIQUEIDENTIFIER) --AddressGuid
		,	@PARTNERGUID		--PartnerGuid
		)
	END
END
